package cn.com.headfree.tjfx.dao.impl;

import cn.com.headfree.entity.TEmpBaseInfo;
import cn.com.headfree.entity.TOrgDept;
import cn.com.headfree.tjfx.dao.IAnalysisDao;
import cn.com.headfree.tjfx.vo.zjrcVo;
import cn.com.headfree.tjfx.vo.zyzgqkVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;
import com.gta.edu.sdk.util.StringUtil;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import javax.sql.RowSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Repository("analysisDao")
public class AnalysisDaoImpl extends BaseDaoImpl<TEmpBaseInfo> implements IAnalysisDao {
	private static final Logger LOG = Logger.getLogger(AnalysisDaoImpl.class);

	//公用查询方法
	public List<HashMap> queryList(StringBuilder sqlBuilder,List<Object> paramList){
		List result = new ArrayList();
		try {
			RowSet rs = queryset(sqlBuilder.toString(), paramList.toArray());
			if (null != rs) {
				ResultSetMetaData metaData = rs.getMetaData();// 获得结果集结构信息（元数据）
				int columnNum = metaData.getColumnCount();// ResultSet列数
				while (rs.next()) {
					HashMap<Object, Object> map = new HashMap<Object, Object>();
					for (int i = 1; i <= columnNum; i++) {
						// rs.getObject(i) 获得当前行某一列字段的值
						map.put(metaData.getColumnName(i).toLowerCase(), rs.getObject(i));
					}
					result.add(map);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("查询出现了异常,查询语句：" + sqlBuilder.toString(), e);
		}
		return result;
	}

	//根据id查询子单位或部门信息
	@Override
	public List<HashMap> getChildOrgDeptByid(String id){
		StringBuilder sqlBuilder = new StringBuilder(" select tod.* from t_org_dept tod where tod.p_id= ? ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(id);
		return this.queryList(sqlBuilder,paramList);
	}

	@Override
	public TOrgDept getDeptsById(String id) {
		String sql = "select id,name,limit_start,limit_end,p_id,path,level from t_org_dept where 1=1 ";
		StringBuilder sqlbuilder = new StringBuilder(sql);
		ArrayList<Object> arrayList = new ArrayList<Object>();
		sqlbuilder.append(" AND id = ? ");
		arrayList.add(id);
		sqlbuilder.append(" order by level ,sort ");
		return this.findUnique(TOrgDept.class, sqlbuilder.toString() , arrayList.toArray());
	}

	//专家人才-柱状图列表
	@Override
	public List<HashMap> getZjrcfbtListByOrgId(String parentOrgId){
		StringBuilder sqlBuilder = new StringBuilder(" select a.level_name, " +
//				"GET_DIC('ExpertLevel',a.level_name) as role_name," +
				"b.org_id,c.name,count(a.level_name) as allnum " +
				"from t_expert_title_info a " +
				"left join t_dept_emp_rela b on a.erp_no=b.erp_no " +
				"LEFT JOIN t_org_dept c ON c.id = b.org_id " +
				"where a.del_sign=0 " +
				"and a.is_valid=0 " +
				"and a.is_cover=0 " +
				"and a.flow_status=1 " +
				"and b.org_id in (select tod.id from t_org_dept tod " +
				"where tod.p_id= ? ) " +
				"group by a.level_name,b.org_id " +
				"order by a.level_name,b.org_id ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(parentOrgId);
		return this.queryList(sqlBuilder,paramList);
	}

	//信息预览-专家人才-柱状图
	@Override
	public List<HashMap> getPreViewZjrcfbtListByOrgId(String parentOrgId){
		StringBuilder sqlBuilder = new StringBuilder(" select a.level_name, " +
//				"GET_DIC('ExpertLevel',a.level_name) as role_name," +
				"count(a.level_name) as allnum " +
				"from t_expert_title_info a " +
				"left join t_dept_emp_rela b on a.erp_no=b.erp_no " +
				"where a.del_sign=0 " +
				"and a.is_valid=0 " +
				"and a.is_cover=0 " +
				"and a.flow_status=1 " +
				"and b.org_id in (select tod.id from t_org_dept tod " +
				"where tod.p_id= ? ) " +
				"group by a.level_name " +
				"order by a.level_name ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(parentOrgId);
		return this.queryList(sqlBuilder,paramList);
	}

	//专家人才列表
	@Override
	public void findZjrcList(Page<zjrcVo> page, String parentOrgId, zjrcVo zjrc) {
		String sql = "  SELECT " +
				"a.erp_no," +
				"a.user_name, " +
				"b.unit as org_name, " +
				"b.sex, " +
				"b.birth_date, " +
				"a.major, " +
				"a.emp_title, " +
				"a.acqu_time, " +
				"a.level_name, " +
				"a.type, " +
				"c.is_cont_educ," +
				"case when c.is_cont_educ='1' then '是' when c.is_cont_educ='0' then '否' else null end as is_cont_educ_name, " +
				"c.educ_name " +
				"FROM " +
				"t_expert_title_info a " +
				"LEFT JOIN t_emp_base_info b ON a.erp_no = b.erp_no " +
				"left join t_educate_info c on a.erp_no=c.erp_no and c.is_valid = '0' and c.del_sign = '0' and c.is_highest_educ=0 " +
				"WHERE " +
				"a.del_sign = 0 " +
				"AND a.is_valid = 0 " +
				"AND a.is_cover = 0 " +
				"AND a.flow_status = 1  " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(parentOrgId)) {
			sqlBuilder.append(" AND b.org_id IN ( SELECT tod.id FROM t_org_dept tod WHERE tod.p_id = ? ) ");
			arrayList.add(parentOrgId);
		}
		//姓名
		if (!StringUtil.isNullOrEmpty(zjrc.getUserName())) {
			sqlBuilder.append(" AND a.user_name like concat('%',?,'%') ");
			arrayList.add(zjrc.getUserName());
		}
		//性别
		if (!StringUtil.isNullOrEmpty(zjrc.getSex())) {
			sqlBuilder.append(" AND b.sex = ? ");
			arrayList.add(zjrc.getSex());
		}
		//工作单位
		if (!StringUtil.isNullOrEmpty(zjrc.getOrgId())) {
			sqlBuilder.append(" AND b.org_id = ? ");
			arrayList.add(zjrc.getOrgId());
		}
		//是否全日制
		if (!StringUtil.isNullOrEmpty(zjrc.getIsContEduc())) {
			String isContEduc = zjrc.getIsContEduc();
			if(isContEduc.equals("是")){
				sqlBuilder.append(" AND c.is_cont_educ='1' ");
			}else{
				sqlBuilder.append(" AND c.is_cont_educ!='1' ");
			}
		}
		//类别
		if (!StringUtil.isNullOrEmpty(zjrc.getType())) {
			sqlBuilder.append(" AND a.type = ? ");
			arrayList.add(zjrc.getType());
		}
		//专业
		if (!StringUtil.isNullOrEmpty(zjrc.getMajor())) {
			sqlBuilder.append(" AND a.major = ? ");
			arrayList.add(zjrc.getMajor());
		}
		//专家等级
		if (!StringUtil.isNullOrEmpty(zjrc.getLevelName())) {
			sqlBuilder.append(" AND a.level_name = ? ");
			arrayList.add(zjrc.getLevelName());
		}
		//人才称号名称
		if (!StringUtil.isNullOrEmpty(zjrc.getEmpTitle())) {
			sqlBuilder.append(" AND a.emp_title = ? ");
			arrayList.add(zjrc.getEmpTitle());
		}

		sqlBuilder.append(" order by b.unit  ");
		this.findPageBySql(zjrcVo.class, sqlBuilder.toString(), page, arrayList.toArray());
	}

	//专家人才列表
	@Override
	public List<zjrcVo> findZjrcList(String parentOrgId, zjrcVo zjrc) {
		String sql = "  SELECT " +
				"a.erp_no," +
				"a.user_name, " +
				"b.unit as org_name, " +
				"b.sex, " +
				"b.birth_date, " +
				"a.major, " +
				"a.emp_title, " +
				"a.acqu_time, " +
				"a.level_name, " +
				"a.type, " +
				"c.is_cont_educ," +
				"case when c.is_cont_educ='1' then '是' when c.is_cont_educ='0' then '否' else null end as is_cont_educ_name, " +
				"c.educ_name " +
				"FROM " +
				"t_expert_title_info a " +
				"LEFT JOIN t_emp_base_info b ON a.erp_no = b.erp_no " +
				"left join t_educate_info c on a.erp_no=c.erp_no and c.is_valid = '0' and c.del_sign = '0' and c.is_highest_educ=0 " +
				"WHERE " +
				"a.del_sign = 0 " +
				"AND a.is_valid = 0 " +
				"AND a.is_cover = 0 " +
				"AND a.flow_status = 1  " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(parentOrgId)) {
			sqlBuilder.append(" AND b.org_id IN ( SELECT tod.id FROM t_org_dept tod WHERE tod.p_id = ? ) ");
			arrayList.add(parentOrgId);
		}
		//姓名
		if (!StringUtil.isNullOrEmpty(zjrc.getUserName())) {
			sqlBuilder.append(" AND a.user_name like concat('%',?,'%') ");
			arrayList.add(zjrc.getUserName());
		}
		//性别
		if (!StringUtil.isNullOrEmpty(zjrc.getSex())) {
			sqlBuilder.append(" AND b.sex = ? ");
			arrayList.add(zjrc.getSex());
		}
		//工作单位
		if (!StringUtil.isNullOrEmpty(zjrc.getOrgId())) {
			sqlBuilder.append(" AND b.org_id = ? ");
			arrayList.add(zjrc.getOrgId());
		}
		//是否全日制
		if (!StringUtil.isNullOrEmpty(zjrc.getIsContEduc())) {
			String isContEduc = zjrc.getIsContEduc();
			if(isContEduc.equals("是")){
				sqlBuilder.append(" AND c.is_cont_educ='1' ");
			}else{
				sqlBuilder.append(" AND c.is_cont_educ!='1' ");
			}
		}
		//类别
		if (!StringUtil.isNullOrEmpty(zjrc.getType())) {
			sqlBuilder.append(" AND a.type = ? ");
			arrayList.add(zjrc.getType());
		}
		//专业
		if (!StringUtil.isNullOrEmpty(zjrc.getMajor())) {
			sqlBuilder.append(" AND a.major = ? ");
			arrayList.add(zjrc.getMajor());
		}
		//专家等级
		if (!StringUtil.isNullOrEmpty(zjrc.getLevelName())) {
			sqlBuilder.append(" AND a.level_name = ? ");
			arrayList.add(zjrc.getLevelName());
		}
		//人才称号名称
		if (!StringUtil.isNullOrEmpty(zjrc.getEmpTitle())) {
			sqlBuilder.append(" AND a.emp_title = ? ");
			arrayList.add(zjrc.getEmpTitle());
		}

		sqlBuilder.append(" order by b.unit  ");
		return this.findAllBySQL(zjrcVo.class, sqlBuilder.toString(), null,false, arrayList.toArray());
	}

	//执业资格情况-柱状图列表
	@Override
	public List<HashMap> getZyzgqkListByOrgId(String parentOrgId){
		StringBuilder sqlBuilder = new StringBuilder(" select a.subj_level, " +
//				"GET_DIC('ProfessionalQualifications',a.subj_level) as subj_level_name," +
				"b.org_id,c.name,count(a.subj_level) as allnum " +
				"from t_pro_tec_info a " +
				"left join t_dept_emp_rela b on a.erp_no=b.erp_no " +
				"LEFT JOIN t_org_dept c ON c.id = b.org_id " +
				"where a.del_sign=0 " +
				"and a.is_valid=0 " +
				"and a.is_cover=0 " +
				"and a.flow_status=1 " +
				"and b.org_id in (select tod.id from t_org_dept tod " +
				"where tod.p_id= ? ) " +
				"group by a.subj_level,b.org_id " +
				"order by a.subj_level,b.org_id ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(parentOrgId);
		return this.queryList(sqlBuilder,paramList);
	}

	//信息预览-执业资格情况-柱状图
	@Override
	public List<HashMap> getPreViewZyzgqkListByOrgId(String parentOrgId){
		StringBuilder sqlBuilder = new StringBuilder(" select a.subj_level, " +
//				"GET_DIC('ProfessionalQualifications',a.subj_level) as subj_level_name," +
				"count(a.subj_level) as allnum " +
				"from t_pro_tec_info a " +
				"left join t_dept_emp_rela b on a.erp_no=b.erp_no " +
				"where a.del_sign=0 " +
				"and a.is_valid=0 " +
				"and a.is_cover=0 " +
				"and a.flow_status=1 " +
				"and b.org_id in (select tod.id from t_org_dept tod " +
				"where tod.p_id= ? ) " +
				"group by a.subj_level " +
				"order by a.subj_level ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(parentOrgId);
		return this.queryList(sqlBuilder,paramList);
	}

	//执业资格情况列表
	@Override
	public void findZyzgqkList(Page<zyzgqkVo> page, String parentOrgId, zyzgqkVo zyzgqk) {
		String sql = "  SELECT " +
				"a.erp_no," +
				"a.user_name, " +
				"b.unit as org_name, " +
				"b.sex, " +
				"b.birth_date, " +
				"a.subj_name, " +
				"a.acqu_time, " +
				"a.subj_set, " +
				"a.subj_level, " +
				"a.audit_org, " +
				"a.subj_level, " +
				"a.cert_no," +
				"a.org_level," +
				"c.is_cont_educ," +
				"case when c.is_cont_educ='1' then '是' when c.is_cont_educ='0' then '否' else null end as is_cont_educ_name, " +
				"c.educ_name " +
				"FROM " +
				"t_pro_tec_info a " +
				"LEFT JOIN t_emp_base_info b ON a.erp_no = b.erp_no " +
				"left join t_educate_info c on a.erp_no=c.erp_no and c.is_valid = '0' and c.del_sign = '0' and c.is_highest_educ=0 " +
				"WHERE " +
				"a.del_sign = 0 " +
				"AND a.is_valid = 0 " +
				"AND a.is_cover = 0 " +
				"AND a.flow_status = 1  " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(parentOrgId)) {
			sqlBuilder.append(" AND b.org_id IN ( SELECT tod.id FROM t_org_dept tod WHERE tod.p_id = ? ) ");
			arrayList.add(parentOrgId);
		}
		//姓名
		if (!StringUtil.isNullOrEmpty(zyzgqk.getUserName())) {
			sqlBuilder.append(" AND a.user_name like concat('%',?,'%') ");
			arrayList.add(zyzgqk.getUserName());
		}
		//性别
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSex())) {
			sqlBuilder.append(" AND b.sex = ? ");
			arrayList.add(zyzgqk.getSex());
		}
		//工作单位
		if (!StringUtil.isNullOrEmpty(zyzgqk.getOrgId())) {
			sqlBuilder.append(" AND b.org_id = ? ");
			arrayList.add(zyzgqk.getOrgId());
		}
		//是否全日制
		if (!StringUtil.isNullOrEmpty(zyzgqk.getIsContEduc())) {
			String isContEduc = zyzgqk.getIsContEduc();
			if(isContEduc.equals("是")){
				sqlBuilder.append(" AND c.is_cont_educ='1' ");
			}else{
				sqlBuilder.append(" AND c.is_cont_educ!='1' ");
			}
		}
		//专业技术资格名称
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSubjName())) {
			sqlBuilder.append(" AND a.subj_name like concat('%',?,'%') ");
			arrayList.add(zyzgqk.getSubjName());
		}
		//专业技术资格系列
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSubjSet())) {
			sqlBuilder.append(" AND a.subj_set = ? ");
			arrayList.add(zyzgqk.getSubjSet());
		}
		//专业技术资格等级
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSubjLevel())) {
			sqlBuilder.append(" AND a.subj_level = ? ");
			arrayList.add(zyzgqk.getSubjLevel());
		}
		//发证单位
		if (!StringUtil.isNullOrEmpty(zyzgqk.getAuditOrg())) {
			sqlBuilder.append(" AND a.audit_org like concat('%',?,'%') ");
			arrayList.add(zyzgqk.getAuditOrg());
		}

		sqlBuilder.append(" order by b.unit  ");
		this.findPageBySql(zyzgqkVo.class, sqlBuilder.toString(), page, arrayList.toArray());
	}

	//执业资格情况列表
	@Override
	public List<zyzgqkVo> findZyzgqkList(String parentOrgId, zyzgqkVo zyzgqk) {
		String sql = "  SELECT " +
				"a.erp_no," +
				"a.user_name, " +
				"b.unit as org_name, " +
				"b.sex, " +
				"b.birth_date, " +
				"a.subj_name, " +
				"a.acqu_time, " +
				"a.subj_set, " +
				"a.subj_level, " +
				"a.audit_org, " +
				"a.subj_level, " +
				"a.cert_no," +
				"a.org_level," +
				"c.is_cont_educ," +
				"case when c.is_cont_educ='1' then '是' when c.is_cont_educ='0' then '否' else null end as is_cont_educ_name, " +
				"c.educ_name " +
				"FROM " +
				"t_pro_tec_info a " +
				"LEFT JOIN t_emp_base_info b ON a.erp_no = b.erp_no " +
				"left join t_educate_info c on a.erp_no=c.erp_no and c.is_valid = '0' and c.del_sign = '0' and c.is_highest_educ=0 " +
				"WHERE " +
				"a.del_sign = 0 " +
				"AND a.is_valid = 0 " +
				"AND a.is_cover = 0 " +
				"AND a.flow_status = 1  " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(parentOrgId)) {
			sqlBuilder.append(" AND b.org_id IN ( SELECT tod.id FROM t_org_dept tod WHERE tod.p_id = ? ) ");
			arrayList.add(parentOrgId);
		}
		//姓名
		if (!StringUtil.isNullOrEmpty(zyzgqk.getUserName())) {
			sqlBuilder.append(" AND a.user_name like concat('%',?,'%') ");
			arrayList.add(zyzgqk.getUserName());
		}
		//性别
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSex())) {
			sqlBuilder.append(" AND b.sex = ? ");
			arrayList.add(zyzgqk.getSex());
		}
		//工作单位
		if (!StringUtil.isNullOrEmpty(zyzgqk.getOrgId())) {
			sqlBuilder.append(" AND b.org_id = ? ");
			arrayList.add(zyzgqk.getOrgId());
		}
		//是否全日制
		if (!StringUtil.isNullOrEmpty(zyzgqk.getIsContEduc())) {
			String isContEduc = zyzgqk.getIsContEduc();
			if(isContEduc.equals("是")){
				sqlBuilder.append(" AND c.is_cont_educ='1' ");
			}else{
				sqlBuilder.append(" AND c.is_cont_educ!='1' ");
			}
		}
		//专业技术资格名称
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSubjName())) {
			sqlBuilder.append(" AND a.subj_name like concat('%',?,'%') ");
			arrayList.add(zyzgqk.getSubjName());
		}
		//专业技术资格系列
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSubjSet())) {
			sqlBuilder.append(" AND a.subj_set = ? ");
			arrayList.add(zyzgqk.getSubjSet());
		}
		//专业技术资格等级
		if (!StringUtil.isNullOrEmpty(zyzgqk.getSubjLevel())) {
			sqlBuilder.append(" AND a.subj_level = ? ");
			arrayList.add(zyzgqk.getSubjLevel());
		}
		//发证单位
		if (!StringUtil.isNullOrEmpty(zyzgqk.getAuditOrg())) {
			sqlBuilder.append(" AND a.audit_org like concat('%',?,'%') ");
			arrayList.add(zyzgqk.getAuditOrg());
		}
		sqlBuilder.append(" order by b.unit  ");
		return this.findAllBySQL(zyzgqkVo.class, sqlBuilder.toString(), null,false, arrayList.toArray());
	}
}
